package org.biojava.services.das.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

public class TypesDAO extends SimpleJdbcDaoSupport {
	public static String ontologyKey="cvId";
	public static String autoIdKey="auto_id";
	
	/**
	 * get all the ontology ids from the types table and have them associated
	 * with auto_ids of sources in registry table
	 * 
	 * @return
	 */
	public Map<String, Set<Long>> getTypesOntologiesWithAutoId() {
		// many methods for getting all cvIds and auto_ids can be put in here.
		List<Map<String, Long>> ecos = this.getTypesECOWithAutoId();
		List<Map<String,Long>> cvIds = this.getTypesCvIdWithAutoId();
		List<Map<String, Long>> sos = this.getTypesOntologyFromIdWithAutoId();
		Map<String, Set<Long>> allOntologys=null;
		ecos.addAll(cvIds);
		ecos.addAll(sos);
			allOntologys = this.consolidateOntologies(ecos);
		
		
		return allOntologys;

	}

	private List<Map<String, Long>> getTypesCvIdWithAutoId() {
		List<Map<String, Long>> ontologyToAutoIdList=new ArrayList<Map<String, Long>>();
		String sqlForCvIds = "select cvId, auto_id from types where cvId is not null and cvId like \"%:%\"";
		//System.out.println(getJdbcTemplate().queryForList(sqlForCvIds));
		List<Map<String, Object>> list = getJdbcTemplate().queryForList(
				sqlForCvIds);
		for (Map<String, Object> entry : list) {
			String ontology=(String) entry.get("cvId");
			Long auto_id=(Long)entry.get("auto_id");
		Map map=new HashMap();
		map.put(ontologyKey, ontology);
		map.put(autoIdKey, auto_id);
		ontologyToAutoIdList.add(map);
		}
		
		return ontologyToAutoIdList;
	}

	private List<Map<String, Long>> getTypesECOWithAutoId() {
		String ecoSql = "select category, auto_id from types where category like \"%ECO:%\"";
		// System.out.println(getJdbcTemplate().queryForList(ecoSql ));
		List<Map<String, Long>> ontologyToAutoIdList=new ArrayList<Map<String, Long>>();
		HashMap<String, Set<Long>> ontologyToAutoId = new HashMap<String, Set<Long>>();

		List<Map<String, Object>> list = getJdbcTemplate().queryForList(ecoSql);
		for (Map<String, Object> entry : list) {
			// System.out.println(entry);
			String ecoString = (String) entry.get("category");
			// System.out.println(ecoString);
			if (ecoString.contains("(") && ecoString.contains(")")) {
				// System.out.println(ecoString.indexOf("ECO:"));
				// System.out.println(ecoString.indexOf("ECO:")+7);
				ecoString = ecoString.substring(ecoString.indexOf("(") + 1,
						ecoString.indexOf(")"));
			}
			//System.out.println(ecoString+"|"+entry.get("auto_id"));
			Map map=new HashMap();
			map.put(ontologyKey, ecoString);
			map.put(autoIdKey, entry.get("auto_id"));
			ontologyToAutoIdList.add(map);
		}
		return ontologyToAutoIdList;
	}
	
	
	private List<Map<String, Long>> getTypesOntologyFromIdWithAutoId() {
		String sql = "select id, auto_id from types where id like \"%SO:%\"";
		// System.out.println(getJdbcTemplate().queryForList(ecoSql ));
		List<Map<String, Long>> ontologyToAutoIdList=new ArrayList<Map<String, Long>>();
		List<Map<String, Object>> list = getJdbcTemplate().queryForList(sql);
		for (Map<String, Object> entry : list) {
			// System.out.println(entry);
			String ecoString = (String) entry.get("id");
			// System.out.println(ecoString);
			if (ecoString.contains("(") && ecoString.contains(")")) {
				// System.out.println(ecoString.indexOf("ECO:"));
				// System.out.println(ecoString.indexOf("ECO:")+7);
				ecoString = ecoString.substring(ecoString.indexOf("(") + 1,
						ecoString.indexOf(")"));
			}
			//System.out.println(ecoString+"|"+entry.get("auto_id"));
			Map map=new HashMap();
			map.put(ontologyKey, ecoString);
			map.put(autoIdKey, entry.get("auto_id"));
			ontologyToAutoIdList.add(map);
		}
		return ontologyToAutoIdList;
	}
	
	/**
	 * creates a unique list of ontology ids with associated auto_ids as a unique set
	 * @param allOntologyToAutoIds
	 * @return
	 */
	private Map<String, Set<Long>> consolidateOntologies(List<Map<String, Long>> allOntologyToAutoIds){
		HashMap<String, Set<Long>> ontologyToAutoId = new HashMap<String, Set<Long>>();
		
		for(Map map:allOntologyToAutoIds){
			String ontology=(String) map.get(ontologyKey);
			Long auto_id=(Long)map.get(autoIdKey);
		
		if (ontologyToAutoId.containsKey(ontology)) {
			Set auto_ids = ontologyToAutoId.get(ontology);
			auto_ids.add(auto_id);
		} else {
			Set newSet = new HashSet();
			newSet.add(auto_id);
			ontologyToAutoId.put(ontology, newSet);
		}
		
		}
	
	for (String key : ontologyToAutoId.keySet()) {
		System.out.println(key + ontologyToAutoId.get(key));
	}
		return ontologyToAutoId;
		
	}
	

}
